/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Produk;
import model.Koperasi;
import java.util.HashMap;
import model.Search;

/**
 *
 * @author RavenCrest
 */
public class DBClass {

    public Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/estonion", "root", "nbuser");
        return connection;
    }
    //we get values from servlet by passing produk object to insertdetails method

    public int insertProduk(Produk bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO `produk`(`namaproduk`, `hargaproduk`, `jenisproduk`, `deskripsiproduk`) VALUES (?,?,?,?)");
            PreparedStatement pstmt1 = con.prepareStatement("SET @idproduk = LAST_INSERT_ID()");
            PreparedStatement pstmt2 = con.prepareStatement("INSERT INTO `koperasiproduk`(`idproduk`,`idkoperasi`) VALUES (@idproduk,?)");
            //set values to prepared statement object by getting values from produk object
            pstmt.setString(1, bb.getnamaproduk());
            pstmt.setString(2, bb.gethargaproduk());
            pstmt.setString(3, bb.getjenisproduk());
            pstmt.setString(4, bb.getdeskripsiproduk());
            pstmt2.setString(1, bb.getidkopf());
            System.out.println(pstmt);
            int i = pstmt.executeUpdate();
            int j = pstmt1.executeUpdate();
            int k = pstmt2.executeUpdate();
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public List getAllproduk() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT p.idproduk, p.namaproduk, p.hargaproduk, p.jenisproduk, p.deskripsiproduk, k.namakoperasi "
                + "FROM produk as p "
                + "INNER JOIN koperasiproduk as kp "
                + "ON p.idproduk = kp.idproduk "
                + "INNER JOIN koperasi as k "
                + "ON k.idkoperasi = kp.idkoperasi");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Produk uproduk = new Produk();
            uproduk.setidproduk(rs.getString(1));
            uproduk.setnamaproduk(rs.getString(2));
            uproduk.sethargaproduk(rs.getString(3));
            uproduk.setjenisproduk(rs.getString(4));
            uproduk.setdeskripsiproduk(rs.getString(5));
            uproduk.setidkopf(rs.getString(6));
            list.add(uproduk);

        }
        return list;
    }

    public Produk getProduk(String idproduk) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select `namaproduk`, `hargaproduk`, `jenisproduk`, `deskripsiproduk`, `idproduk` from produk where idproduk=?");
        pstmt.setString(1, idproduk);
        ResultSet rs = pstmt.executeQuery();

        Produk uproduk = new Produk();
        while (rs.next()) {

            uproduk.setnamaproduk(rs.getString(1));
            uproduk.sethargaproduk(rs.getString(2));
            uproduk.setjenisproduk(rs.getString(3));
            uproduk.setdeskripsiproduk(rs.getString(4));
            uproduk.setidproduk(rs.getString(5));
        }

        uproduk.setAction("Update Produk");
        System.out.println(idproduk);
        return uproduk;

    }

    public void UpdateProduk(Produk uproduk, String idproduk) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update produk set namaproduk=?,hargaproduk=?,jenisproduk=?,deskripsiproduk=? where idproduk=? ");
        PreparedStatement pstmt1 = con.prepareStatement("update koperasiproduk set idkoperasi=? where idproduk=?");
        //set values to prepared statement object by getting values from produk object
        pstmt.setString(1, uproduk.getnamaproduk());
        pstmt.setString(2, uproduk.gethargaproduk());
        pstmt.setString(3, uproduk.getjenisproduk());
        pstmt.setString(4, uproduk.getdeskripsiproduk());
        pstmt.setString(5, idproduk);
        pstmt1.setString(1, uproduk.getidkopf());
        pstmt1.setString(2, idproduk);
        pstmt.executeUpdate();
        System.out.println(pstmt1);
        pstmt1.executeUpdate();

    }

    public void deleteproduk(String idproduk) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from produk where idproduk=?");
        pstmt.setString(1, idproduk);
        pstmt.executeUpdate();
    }

    public List getJenis() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from jenis");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Produk uproduk = new Produk();
            uproduk.setjenisambil(rs.getString(1));
            list.add(uproduk);

        }
        return list;
    }

    public List getKota() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from `kota` ORDER BY `kota`.`NamaKota` ASC");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Koperasi ukoperasi = new Koperasi();
            ukoperasi.setKota(rs.getString(1));
            list.add(ukoperasi);

        }
        return list;
    }
    
    public boolean cekLogin(String username, String password) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select userName, password from akun where userName=? AND password=md5(?)");
        pstmt.setString(1, username);
        pstmt.setString(2, password + "estonion#!12314");
        ResultSet rs = pstmt.executeQuery();


        if (rs.next()) {
            return true;

        } else {
            return false;
        }
    }

    public List getAllkoperasi() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from koperasi");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Koperasi ukoperasi = new Koperasi();
            ukoperasi.setidkoperasi(rs.getString(1));
            ukoperasi.setnamakoperasi(rs.getString(2));
            ukoperasi.setalamatkoperasi(rs.getString(3));
            ukoperasi.setdeskripsikoperasi(rs.getString(4));
            ukoperasi.setnotelpkoperasi(rs.getString(5));
            ukoperasi.setKota(rs.getString(6));
            list.add(ukoperasi);

        }
        return list;
    }

    public int insertKoperasi(Koperasi bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("insert into koperasi(`namakoperasi`, `alamatkoperasi`, `deskripsikoperasi`, `notelpkoperasi`, `NamaKota`) values(?,?,?,?,?)");
            //set values to prepared statement object by getting values from koperasi object
            pstmt.setString(1, bb.getnamakoperasi());
            pstmt.setString(2, bb.getalamatkoperasi());
            pstmt.setString(3, bb.getdeskripsikoperasi());
            pstmt.setString(4, bb.getnotelpkoperasi());
            pstmt.setString(5, bb.getKota());
            int i = pstmt.executeUpdate();
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public Koperasi getKoperasi(String idkoperasi) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select `namakoperasi`, `alamatkoperasi`, `deskripsikoperasi`, `notelpkoperasi`, `idkoperasi`, `NamaKota` from koperasi where idkoperasi=?");
        pstmt.setString(1, idkoperasi);
        ResultSet rs = pstmt.executeQuery();

        Koperasi ukoperasi = new Koperasi();
        while (rs.next()) {

            ukoperasi.setnamakoperasi(rs.getString(1));
            ukoperasi.setalamatkoperasi(rs.getString(2));
            ukoperasi.setdeskripsikoperasi(rs.getString(3));
            ukoperasi.setnotelpkoperasi(rs.getString(4));
            ukoperasi.setidkoperasi(rs.getString(5));
            ukoperasi.setKota(rs.getString(6));
        }
        ukoperasi.setActionk("Update Koperasi");
        return ukoperasi;
    }

    public void UpdateKoperasi(Koperasi ukoperasi, String idkoperasi) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update koperasi set namakoperasi=?,alamatkoperasi=?,deskripsikoperasi=?,notelpkoperasi=?,NamaKota=? where idkoperasi=? ");
        //set values to prepared statement object by getting values from koperasi object
        pstmt.setString(1, ukoperasi.getnamakoperasi());
        pstmt.setString(2, ukoperasi.getalamatkoperasi());
        pstmt.setString(3, ukoperasi.getdeskripsikoperasi());
        pstmt.setString(4, ukoperasi.getnotelpkoperasi());
        pstmt.setString(5, ukoperasi.getKota());
        pstmt.setString(6, idkoperasi);
        pstmt.executeUpdate();

    }

    public void deletekoperasi(String idkoperasi) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from koperasi where idkoperasi=?");
        pstmt.setString(1, idkoperasi);
        pstmt.executeUpdate();
    }

    public List getKoperasiList() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select `idkoperasi`,`namakoperasi` from koperasi");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Koperasi ukoperasi = new Koperasi();
            ukoperasi.setidkoperasi(rs.getString(1));
            ukoperasi.setnamakoperasi(rs.getString(2));
            list.add(ukoperasi);

        }
        return list;
    }
    
    public List getKotaKoperasi() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT DISTINCT k.NamaKota FROM koperasi as k INNER JOIN kota as kp ON k.NamaKota = kp.NamaKota");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Koperasi ukoperasi = new Koperasi();
            ukoperasi.setKota(rs.getString(1));
            list.add(ukoperasi);

        }
        return list;
    }

    public List SearchAll(String inputsearch) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT p.idproduk,p.namaproduk,p.hargaproduk,p.jenisproduk,k.namakoperasi FROM produk as p INNER JOIN koperasiproduk as kp ON p.idproduk = kp.idproduk INNER JOIN koperasi as k ON k.idkoperasi = kp.idkoperasi WHERE k.namakoperasi LIKE ? OR p.namaproduk LIKE ?");
        pstmt.setString(1, "%" + inputsearch + "%");
        pstmt.setString(2, "%" + inputsearch + "%");
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Search usearch = new Search();
            usearch.setidproduk(rs.getString(1));
            usearch.setnamaproduk(rs.getString(2));
            usearch.sethargaproduk(rs.getString(3));
            usearch.setjenisproduk(rs.getString(4));
            usearch.setnamakoperasi(rs.getString(5));
            list.add(usearch);
        }
        return list;

    }

    public List SearchJenis(String inputsearch) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM produk WHERE jenisproduk LIKE ?");
        pstmt.setString(1, inputsearch);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Search usearch = new Search();
            usearch.setidproduk(rs.getString(1));
            usearch.setnamaproduk(rs.getString(2));
            usearch.sethargaproduk(rs.getString(3));
            usearch.setjenisproduk(rs.getString(4));
            usearch.setdeskripsiproduk(rs.getString(5));
            list.add(usearch);
        }
        return list;

    }

    public List SearchHarga(String range1, String range2) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM produk WHERE hargaproduk BETWEEN ? AND ?");
        pstmt.setString(1, range1);
        pstmt.setString(2, range2);
        System.out.println(pstmt);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Search usearch = new Search();
            usearch.setidproduk(rs.getString(1));
            usearch.setnamaproduk(rs.getString(2));
            usearch.sethargaproduk(rs.getString(3));
            usearch.setjenisproduk(rs.getString(4));
            usearch.setdeskripsiproduk(rs.getString(5));
            list.add(usearch);
        }
        return list;

    }
    
    public List SearchKota(String inputsearch) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM koperasi WHERE NamaKota=?");
        pstmt.setString(1, inputsearch);
        System.out.println(pstmt);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Search usearch = new Search();
            usearch.setidkoperasi(rs.getString(1));
            usearch.setnamakoperasi(rs.getString(2));
            usearch.setalamatkoperasi(rs.getString(3));
            usearch.setdeskripsikoperasi(rs.getString(4));
            usearch.setnotelpkoperasi(rs.getString(5));
            usearch.setKota(rs.getString(6));
            list.add(usearch);
        }
        return list;

    }
    
    public List getLinkKoperasi(String idproduk) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT k.namakoperasi, k.alamatkoperasi, k.notelpkoperasi "
                + "FROM koperasi as k "
                + "INNER JOIN koperasiproduk as kp "
                + "ON k.idkoperasi = kp.idkoperasi "
                + "INNER JOIN produk as p "
                + "ON p.idproduk = kp.idproduk WHERE p.idproduk=? ");
        pstmt.setString(1, idproduk);
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Koperasi ukop = new Koperasi();
            ukop.setnamakoperasi(rs.getString(1));
            ukop.setalamatkoperasi(rs.getString(2));
            ukop.setnotelpkoperasi(rs.getString(3));
            list.add(ukop);

        }
        return list;
    }
    
    public List getLinkProduk(String idkoperasi) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT p.idproduk, p.namaproduk, p.hargaproduk, p.jenisproduk, p.deskripsiproduk, k.namakoperasi "
                + "FROM produk as p "
                + "INNER JOIN koperasiproduk as kp "
                + "ON p.idproduk = kp.idproduk "
                + "INNER JOIN koperasi as k "
                + "ON k.idkoperasi = kp.idkoperasi WHERE k.idkoperasi=?");
        pstmt.setString(1, idkoperasi);
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Produk uproduk = new Produk();
            uproduk.setidproduk(rs.getString(1));
            uproduk.setnamaproduk(rs.getString(2));
            uproduk.sethargaproduk(rs.getString(3));
            uproduk.setjenisproduk(rs.getString(4));
            uproduk.setdeskripsiproduk(rs.getString(5));
            uproduk.setidkopf(rs.getString(6));
            list.add(uproduk);

        }
        return list;
    }
//    public HashMap getSearch() throws SQLException, ClassNotFoundException {
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("select * from propinsi");
//        ResultSet rs = pstmt.executeQuery();
//        HashMap hashmap =  new HashMap();
//        while (rs.next()){
//            Userkoperasi ukoperasi = new Userkoperasi();
//            ukoperasi.setPropinsi(rs.getString(2));
//            hashmap.add(ukoperasi);
//        }
//            return hashmap;
//    }
}
